<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>Mysql主从复制 - 德国粗茶淡饭</title><meta name="Description" content="Mysql主从复制"><meta property="og:title" content="Mysql主从复制" />
<meta property="og:description" content="Mysql主从复制" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://www.ctq6.cn/technology/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" />
<meta property="og:image" content="https://www.ctq6.cn/logo.png"/>
<meta property="article:published_time" content="2021-02-16T19:37:30+08:00" />
<meta property="article:modified_time" content="2021-02-16T19:37:30+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://www.ctq6.cn/logo.png"/>

<meta name="twitter:title" content="Mysql主从复制"/>
<meta name="twitter:description" content="Mysql主从复制"/>
<meta name="application-name" content="LoveIt">
<meta name="apple-mobile-web-app-title" content="LoveIt"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://www.ctq6.cn/technology/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" /><link rel="prev" href="https://www.ctq6.cn/technology/prometheus/prometheus_operator%E4%BD%BF%E7%94%A8/" /><link rel="next" href="https://www.ctq6.cn/technology/mysql/mysql%E4%BA%8B%E5%8A%A1%E8%AF%A6%E8%A7%A3/" /><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "Mysql主从复制",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6\/"
        },"image": [{
                            "@type": "ImageObject",
                            "url": "https:\/\/www.ctq6.cn\/images\/Apple-Devices-Preview.png",
                            "width":  3200 ,
                            "height":  2048 
                        }],"genre": "technology","keywords": "Mysql","wordcount":  1913 ,
        "url": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6\/","datePublished": "2021-02-16T19:37:30+08:00","dateModified": "2021-02-16T19:37:30+08:00","license": "This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher": {
            "@type": "Organization",
            "name": "xxxx","logo": {
                    "@type": "ImageObject",
                    "url": "https:\/\/www.ctq6.cn\/images\/avatar.png",
                    "width":  528 ,
                    "height":  560 
                }},"author": {
                "@type": "Person",
                "name": "MikelPan"
            },"description": "Mysql主从复制"
    }
    </script></head>
    <body header-desktop="fixed" header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('auto' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'auto' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 生活 </a><a class="menu-item" href="/technology/"> 技术 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/categories/documentation/"> 文档 </a><a class="menu-item" href="/about/"> 关于 </a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i>  </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item language" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                        <select class="language-select" id="language-select-desktop" onchange="location = this.value;"><option value="/technology/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" selected>简体中文</option></select>
                    </a><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">生活</a><a class="menu-item" href="/technology/" title="">技术</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/categories/documentation/" title="">文档</a><a class="menu-item" href="/about/" title="">关于</a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i></a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a><a href="javascript:void(0);" class="menu-item" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                    <select class="language-select" onchange="location = this.value;"><option value="/technology/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" selected>简体中文</option></select>
                </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="page single special"><h1 class="single-title animated pulse faster">Mysql主从复制</h1><div class="content" id="content"><h3 id="一mysql主从同步原理">一、mysql主从同步原理</h3>
<p>Mysql主从复制也可以称为Mysql主从同步，它是构建数据库高可用集群架构的基础。它通过将一台主机的数据复制到其他一台或者多台主机上，并重新应用日志（<strong>realy log</strong>）中的SQL语句来实现复制功能。Mysql支持单向，双向，链式级联，异步复制，复制过程中一台服务器充当主库（master），而一个或者多个服务器充当从库（slave）</p>
<h4 id="11主从复制功能">1.1、主从复制功能</h4>
<p>主从复制原理：master服务器上工作线程I/O dump thread，从服务器上两个工作线程，一个是I/O thread，另一个是SQL thread。
主库把外界接收到的SQL请求记录到自己的binlog日志中，从库的I/O thread去请求主库的binlog日志，并将得到的binlog日志写到自己的Realy log（中继日志）文件中。然后在从库上重做应用中继日志中的SQL语句。主库通过I/O dump thread 给从库I/O thread 传送binlog日志。</p>
<h4 id="12复制中的参数详解">1.2、复制中的参数详解</h4>
<ul>
<li>log-bin：搭建主从复制，必须开启二进制日志</li>
<li>server-id：mysql在同一组主从结构中的唯一标识</li>
<li>sever-uuid：存放在数据目录中的auto.cnf中</li>
<li>read only：设置从库为只读转态</li>
<li>binglog_format: 二进制日志的格式，使用row模式</li>
<li>log_salve_updates: 将master服务器上获取的数据信息记录到从服务器的二进制日志文件中</li>
<li>binglog-db-db：选择性复制数据库（在主库上使用）</li>
<li>binglog-ignore-db： 忽略某个库的复制</li>
<li>gtid_mode: gtid模式是否开启，使用gtid模式，设置gtid_mode=on</li>
<li>enforce-gtid-consistency: 使用gtid复制，开启，enforce-gtid-consistency=on</li>
</ul>
<h3 id="二mysql主从复制binlog">二、mysql主从复制（binlog）</h3>
<p>2.1、修改主库配置文件</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">vim /etc/my.cnf
<span class="o">[</span>mysqld<span class="o">]</span>
<span class="c1">####: for binlog</span>
server-id<span class="o">=</span><span class="m">1</span>
<span class="nv">binlog_format</span>                       <span class="o">=</span>row                          <span class="c1">#     row</span>
log-bin                             <span class="o">=</span>/data/mysqlData/binlog/mysql-bin
log-bin-index                       <span class="o">=</span>/data/mysqlData/binlog/mysql-bin.index                      <span class="c1">#      off</span>
<span class="nv">binlog_rows_query_log_events</span>        <span class="o">=</span>on                             <span class="c1">#   off</span>
<span class="nv">log_slave_updates</span>                   <span class="o">=</span>on                             <span class="c1">#   off</span>
<span class="nv">expire_logs_days</span>                    <span class="o">=</span><span class="m">7</span>                              <span class="c1">#   0</span>
<span class="nv">binlog_cache_size</span>                   <span class="o">=</span><span class="m">65536</span>                          <span class="c1">#   65536(64k)</span>
<span class="c1">#binlog_checksum                    =none                           #  CRC32</span>
<span class="nv">sync_binlog</span>                         <span class="o">=</span><span class="m">0</span>                              <span class="c1">#   1</span>
slave-preserve-commit-order         <span class="o">=</span>ON                             <span class="c1">#</span>
</code></pre></td></tr></table>
</div>
</div><p>2.2、主库上执行操作</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 创建主从复制账号</span>
create user <span class="s1">&#39;repl&#39;</span>@<span class="s1">&#39;192.168.5.%&#39;</span> identified by <span class="s1">&#39;repl@2019#pl&#39;</span><span class="p">;</span>
grant replication slave on *.* to <span class="s1">&#39;repl&#39;</span>@<span class="s1">&#39;192.168.5.%&#39;</span><span class="p">;</span>
flush privileges<span class="p">;</span>
<span class="c1"># 导出主库数据</span>
mysqldump --single-transaction -uroot -proot123 --master-data<span class="o">=</span><span class="m">2</span> --flush-logs --events --triggers --routines -A &gt; all.sql
<span class="c1"># 记录binlog文件和position号</span>
head -n <span class="m">30</span> all.sql <span class="p">|</span> grep <span class="s2">&#34;MASTER_LOG_FILE&#34;</span>
head -n <span class="m">30</span> all.sql  <span class="p">|</span> grep <span class="s2">&#34;MASTER_LOG_POS&#34;</span>
<span class="c1"># 备份文件传递到从服务器上</span>
scp all.sql root@slave:/root/
</code></pre></td></tr></table>
</div>
</div><p>2.3、修改从库的配置文件</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="nv">server_id</span>                           <span class="o">=</span> <span class="m">2</span>
binlog-ignore-db                    <span class="o">=</span>mysql
<span class="nv">binlog_format</span>                       <span class="o">=</span>row
log-bin <span class="o">=</span>                           <span class="o">=</span>/data/mysqlData/binlog/slave1-bin
log-bin-index                       <span class="o">=</span>/data/mysqlData/binlog/salve1-bin.index
log-slave-updates                   <span class="o">=</span>on
<span class="nv">expire_logs_days</span>                    <span class="o">=</span><span class="m">7</span>
<span class="nv">sync_binlog</span>                         <span class="o">=</span> <span class="m">0</span>
<span class="nv">relay_log</span>                           <span class="o">=</span>/data/mysqlData/relaylog/relay-bin
<span class="nv">log_slave_updates</span>                   <span class="o">=</span><span class="m">1</span>
</code></pre></td></tr></table>
</div>
</div><p>2.4、配置主从</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 导入数据</span>
mysql -uroot -proot123 &lt; all.sql
<span class="c1"># 重置主从</span>
reset slave all
<span class="c1"># 数据库命名执行配置</span>
CHANGE MASTER TO
<span class="nv">MASTER_HOST</span><span class="o">=</span><span class="s1">&#39;192.168.248.137&#39;</span>,
<span class="nv">MASTER_USER</span><span class="o">=</span><span class="s1">&#39;repl&#39;</span>,
<span class="nv">MASTER_PASSWORD</span><span class="o">=</span><span class="s1">&#39;repl@2019#pl&#39;</span>,
<span class="nv">MASTER_PORT</span><span class="o">=</span>3306,
<span class="nv">MASTER_LOG_FILE</span><span class="o">=</span><span class="s1">&#39;mysql-bin.000004&#39;</span>,
<span class="nv">MASTER_LOG_POS</span><span class="o">=</span>3034<span class="p">;</span>
<span class="c1"># 开启主从</span>
start salve
<span class="c1"># 查看主从复制状态</span>
show slave status<span class="se">\G</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="三mysql主从复制-gtid">三、mysql主从复制 （gtid）</h3>
<h4 id="31修改主库配置文件">3.1、修改主库配置文件</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">vim /etc/my.cnf
<span class="o">[</span>mysqld<span class="o">]</span>
<span class="c1">####: for binlog</span>
server-id<span class="o">=</span><span class="m">1</span>
<span class="nv">binlog_format</span>                       <span class="o">=</span>row                          <span class="c1">#     row</span>
log-bin                             <span class="o">=</span>/data/mysqlData/binlog/mysql-bin
log-bin-index                       <span class="o">=</span>/data/mysqlData/binlog/mysql-bin.index                      <span class="c1">#      off</span>
<span class="nv">binlog_rows_query_log_events</span>        <span class="o">=</span>on                             <span class="c1">#   off</span>
<span class="nv">log_slave_updates</span>                   <span class="o">=</span>on                             <span class="c1">#   off</span>
<span class="nv">expire_logs_days</span>                    <span class="o">=</span><span class="m">7</span>                              <span class="c1">#   0</span>
<span class="nv">binlog_cache_size</span>                   <span class="o">=</span><span class="m">65536</span>                          <span class="c1">#   65536(64k)</span>
<span class="c1">#binlog_checksum                    =none                           #  CRC32</span>
<span class="nv">sync_binlog</span>                         <span class="o">=</span><span class="m">0</span>                              <span class="c1">#   1</span>
slave-preserve-commit-order         <span class="o">=</span>ON                             <span class="c1">#</span>

<span class="c1">####: gitd</span>
gtid-mode <span class="o">=</span> ON
enforce-gtid-consistency <span class="o">=</span> ON
</code></pre></td></tr></table>
</div>
</div><h4 id="32主库上执行操作">3.2、主库上执行操作</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 创建主从复制账号</span>
create user <span class="s1">&#39;repl&#39;</span>@<span class="s1">&#39;192.168.5.%&#39;</span> identified by <span class="s1">&#39;repl@2019#pl&#39;</span>
grant replication slave *.* to <span class="s1">&#39;repl&#39;</span>@<span class="s1">&#39;192.168.5.%&#39;</span>
flush privileges
<span class="c1"># 导出主库数据</span>
mysqldump --single-transaction -uroot -proot123 --opt --master-data<span class="o">=</span><span class="m">2</span> --flush-logs --events --triggers --routines -A &gt; all.sql
</code></pre></td></tr></table>
</div>
</div><h4 id="33修改mysql从服务器配置">3.3、修改mysql从服务器配置</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="nv">server_id</span>                           <span class="o">=</span> <span class="m">2</span>
binlog-ignore-db                    <span class="o">=</span>mysql
<span class="nv">binlog_format</span>                       <span class="o">=</span>row
log-bin <span class="o">=</span>                           <span class="o">=</span>/data/mysqlData/binlog/slave-bin
log-bin-index                       <span class="o">=</span>/data/mysqlData/binlog/salve-bin.index
log-slave-updates                   <span class="o">=</span>on
<span class="nv">expire_logs_days</span>                    <span class="o">=</span><span class="m">7</span>
<span class="nv">sync_binlog</span>                         <span class="o">=</span> <span class="m">0</span>
<span class="nv">relay_log</span>                           <span class="o">=</span>/data/mysqlData/relaylog/relay-bin
<span class="nv">read_only</span>                           <span class="o">=</span><span class="m">1</span>
<span class="nv">log_slave_updates</span>                   <span class="o">=</span><span class="m">1</span>


<span class="c1">####: gitd</span>
gtid-mode <span class="o">=</span> ON
enforce-gtid-consistency <span class="o">=</span> ON
</code></pre></td></tr></table>
</div>
</div><h4 id="34配置主从">3.4、配置主从</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 清空 gtid_executed</span>
reset master
<span class="c1"># 数据导入</span>
mysql -uroot -proot123 &lt; all.sql
<span class="c1"># 配置主从</span>
CHANGE MASTER TO
<span class="nv">MASTER_HOST</span><span class="o">=</span><span class="s1">&#39;192.168.248.137&#39;</span>,
<span class="nv">MASTER_USER</span><span class="o">=</span><span class="s1">&#39;repl&#39;</span>,
<span class="nv">MASTER_PASSWORD</span><span class="o">=</span><span class="s1">&#39;repl@2019#pl&#39;</span>,
<span class="nv">MASTER_PORT</span><span class="o">=</span>3306,
<span class="nv">MASTER_AUTO_POSITION</span> <span class="o">=</span> 1；
<span class="c1"># 开启主从</span>
start slave
<span class="c1"># 查看主从复制状态</span>
show slave status<span class="se">\G</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="35跳过事务">3.5、跳过事务</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">stop slave
<span class="nb">set</span> <span class="nv">gtid_next</span><span class="o">=</span><span class="s1">&#39;f75ae43f-3f5e-11e7-9b98-001c4297532a:20&#39;</span>
begin
commit
<span class="nb">set</span> <span class="nv">gtid_next</span><span class="o">=</span><span class="s1">&#39;AUTOMATIC&#39;</span>
start slave
</code></pre></td></tr></table>
</div>
</div><h3 id="四mysql从传统模式改为gtid">四、mysql从传统模式改为gtid</h3>
<h4 id="41修改全局变量">4.1、修改全局变量</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">1、修改enforce_gtid_consistency为warn
<span class="nb">set</span> global <span class="nv">enforce_gtid_consistency</span><span class="o">=</span>warn<span class="p">;</span>
2、修改enforce_gtid_consistency为on
<span class="nb">set</span> global <span class="nv">enforce_gtid_consistency</span><span class="o">=</span>on<span class="p">;</span>
3、修改gtid模式为off_permissive
<span class="nb">set</span> global <span class="nv">gtid_mode</span><span class="o">=</span>off_permissive<span class="p">;</span>
4、修改gtid模式为on_permissive
<span class="nb">set</span> global <span class="nv">gtid_mode</span><span class="o">=</span>on_permissive<span class="p">;</span>
5、确认从库的onging_anonymous_transaction_count参数是否为0
show global status like <span class="s1">&#39;%ongoing_anonymous_%&#39;</span><span class="p">;</span>
6、开启gtid
<span class="nb">set</span> global <span class="nv">gtid_mode</span><span class="o">=</span>on<span class="p">;</span>
7、开启主从复制
stop slave
change master to <span class="nv">master_auto_position</span><span class="o">=</span>1<span class="p">;</span>
start slave
</code></pre></td></tr></table>
</div>
</div><h4 id="42修改mycnf配置文件">4.2、修改my.cnf配置文件</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 主库添加配置</span>
<span class="nv">gtid_mode</span><span class="o">=</span>on
<span class="nv">enforce_gtid_consistency</span><span class="o">=</span>on
<span class="c1"># 主库添加配置</span>
<span class="nv">gtid_mode</span><span class="o">=</span>on
<span class="nv">enforce_gtid_consistency</span><span class="o">=</span>on
<span class="nv">log_slave_updates</span><span class="o">=</span><span class="m">1</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="43数据导出导入">4.3、数据导出导入</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 主库数据导出</span>
mysqldump --single-transaction -uroot -proot123 --opt --master-data<span class="o">=</span><span class="m">2</span> --flush-logs --events --triggers --routines -A &gt; all.sql
<span class="c1"># 从库数据导入</span>
systemctl restart mysqld
reset 
mysql -uroot -p &lt; all.sql
</code></pre></td></tr></table>
</div>
</div><h4 id="44从库开启主从">4.4、从库开启主从</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">reset master
<span class="c1"># 配置msater主机信息</span>
CHANGE MASTER TO
<span class="nv">MASTER_HOST</span><span class="o">=</span><span class="s1">&#39;192.168.0.12&#39;</span>,
<span class="nv">MASTER_USER</span><span class="o">=</span><span class="s1">&#39;repl&#39;</span>,
<span class="nv">MASTER_PASSWORD</span><span class="o">=</span><span class="s1">&#39;password&#39;</span>,
<span class="nv">MASTER_PORT</span><span class="o">=</span>3306,
<span class="nv">MASTER_AUTO_POSITION</span> <span class="o">=</span> 1<span class="p">;</span>
<span class="c1"># 开启主从</span>
start slave
</code></pre></td></tr></table>
</div>
</div><h4 id="45gtid跳过事件">4.5、gtid跳过事件</h4>
<h5 id="方法一">方法一</h5>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 查看gtid_next的值</span>
show variables like <span class="s1">&#39;%next%&#39;</span><span class="p">;</span>
<span class="c1"># 停止从库</span>
stop slave<span class="p">;</span>
<span class="c1"># 修改gtid为下一个值</span>
<span class="nb">set</span> <span class="nv">gtid_next</span><span class="o">=</span><span class="s1">&#39;6a5a698f-18eb-11e9-afa0-6c92bf45c92e:17&#39;</span><span class="p">;</span>
begin
commit
SET <span class="nv">GTID_NEXT</span><span class="o">=</span><span class="s2">&#34;AUTOMATIC&#34;</span><span class="p">;</span>
start slave<span class="p">;</span>
show slave status<span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h5 id="方法二">方法二</h5>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 重置master</span>
stop slave<span class="p">;</span>
reset master<span class="p">;</span>
SET @@GLOBAL.GTID_PURGED <span class="o">=</span><span class="err">&#39;</span>8f9e146f-0a18-11e7-810a-0050568833c8:1-4<span class="p">;</span>
START SLAVE<span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h5 id="方法三">方法三</h5>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># pt 忽略错误码</span>
pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers<span class="o">=</span><span class="m">1062</span> --user<span class="o">=</span>root --password<span class="o">=</span><span class="s1">&#39;bc.123456&#39;</span>
<span class="c1"># pt 忽略错误信息</span>
pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers<span class="o">=</span><span class="m">1062</span> --user<span class="o">=</span>root --password<span class="o">=</span><span class="s1">&#39;bc.123456&#39;</span>
</code></pre></td></tr></table>
</div>
</div></div><div id="comments"></div></div></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">mikel pan</a></span>&nbsp;|&nbsp;<span class="license"><a rel="license external nofollow noopener noreffer" href="https://creativecommons.org/licenses/by-nc/4.0/" target="_blank">CC BY-NC 4.0</a></span><span class="icp-splitter">&nbsp;|&nbsp;</span><br class="icp-br"/>
                    <span class="icp"><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备2021047442号</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css"><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/autocomplete.js@0.37.1/dist/autocomplete.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/algoliasearch@4.2.0/dist/algoliasearch-lite.umd.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":10},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true},"search":{"algoliaAppID":"REQJX89W85","algoliaIndex":"index.zh-cn","algoliaSearchKey":"63fa048de9b35627f46672e95abc14df","highlightTag":"em","maxResultLength":10,"noResultsFound":"没有找到结果","snippetLength":50,"type":"algolia"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
